package com.choudou5.solr.utils;

import cn.hutool.core.date.DateUtil;
import com.choudou5.solr.bean.common.OrderBean;
import com.choudou5.solr.bean.common.SqlQueryBean;
import com.choudou5.solr.framework.util.RequestUtil;
import com.choudou5.solr.util.LogDeBugUtil;
import com.choudou5.solr.util.StrUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Map;

/**
 * @Name：SqlQueryBuilder 说明
 * @@author choudou5
 * @@date 2018-07-15
 */
public class SqlQueryBuilder extends QueryBuilder {

    private static final Logger logger = LoggerFactory.getLogger(SqlQueryBuilder.class);


    /**
     * 构建 查询语句
     * @param req
     * @return
     */
    public static SqlQueryBean buildWhereSql(HttpServletRequest req) {
        SqlQueryBean sqlQueryBean = new SqlQueryBean();
        StringBuilder where = new StringBuilder();
        //构建查询语句
        buildQueryParam(req, where);
        if (where.length() == 0)
            where.append("1=1");
        LogDeBugUtil.debug("查询条件：" + where.toString());
        sqlQueryBean.setWhere(where.toString());

        //设置排序
        String orderBy = RequestUtil.getStrParameter(req, "page.orderBy");
        String order = RequestUtil.getStrParameter(req, "page.order");
        if(StrUtil.isNotBlank(orderBy) && StrUtil.isNotBlank(order) && !isIncludeSqlInject(order)){
            sqlQueryBean.setOrderBean(new OrderBean(orderBy, order));
            req.setAttribute("orderBean", sqlQueryBean.getOrderBean());
        }

        //设置分页
        sqlQueryBean.setPageBean(buildPageBean(req));
        return sqlQueryBean;
    }


    //构建查询参数
    private static void buildQueryParam(HttpServletRequest req, StringBuilder where){
        Enumeration<String> paramNames = req.getParameterNames();
        String qType = null;
        String[] array = null;
        int dex = 0;
        Map<String, String> searchCondMap = new HashMap<>();
        while (paramNames.hasMoreElements()) {
            String paramName = paramNames.nextElement();
            String paramVal = req.getParameter(paramName);
            if(StrUtil.isNotBlank(paramVal) && paramName.startsWith(Q_F_PREFIX) && !isIncludeSqlInject(paramVal)){
                //格式：QF__{查询类型}__{字段名}
                //查询类型：EQ=等于， NEQ=不等于，BW=范围，EGT=大于等于，ELT=小于等于
                array = StrUtil.split(paramName, "__");
                if(array.length == 3){
                    searchCondMap.put(paramName, paramVal);
                    paramName = array[2];
                    qType = array[1];
                    //等于
                    switch (qType){
                        case "EQ":
                            where.append((dex > 0 ? " AND " : "") + appendCondition(paramName, " = ", paramVal));
                            break;
                        case "NEQ":
                            where.append((dex > 0 ? " AND " : "") + appendCondition(paramName, " !=", paramVal));
                            break;
                        case "LK":
                            where.append((dex > 0 ? " AND " : "") + appendCondition(paramName, " LIKE ",  "%"+paramVal+"%"));
                            break;
                        case "BW":
                            String val = appendCondition(paramName, null, "BW", paramVal);
                            if(val != null) {
                                where.append((dex > 0 ? " AND " : "") + val);
                            }else{
                                dex--;
                            }
                            break;
                        case "EGT":
                            where.append((dex > 0 ? " AND " : "") + appendCondition(paramName, " >= ", paramVal));
                            break;
                        case "ELT":
                            where.append((dex > 0 ? " AND " : "") + appendCondition(paramName, " <= ", paramVal));
                            break;
                    }
                    dex++;
                }
            }
        }
        req.setAttribute("searchCondMap", searchCondMap);
    }

    private static String appendCondition(String paramName, String symbol, String paramVal){
        return appendCondition(paramName, symbol, null, paramVal);
    }

    private static String appendCondition(String paramName, String symbol, String qType, String paramVal){
        if("BW".equals(qType)){
            String[] vals = null;
            if(paramName.contains("Date") || paramName.contains("Time")){
                vals = paramVal.split(" - ");
            }else{
                vals = StrUtil.split(paramVal, ",");
            }
            if(vals.length == 2){
                int state = 0;
                if(StrUtil.isNotBlank(vals[0]))
                    state += 1;
                if(StrUtil.isNotBlank(vals[1]))
                    state += 2;
                if(state==1)
                    return "`" + StrUtil.toSqlField(paramName) + "` > " + wrapToStr(vals[0]);
                if(state==2)
                    return "`" + StrUtil.toSqlField(paramName) + "` < " + wrapToStr(vals[1]);
                if(state==3)
                    return "`" + StrUtil.toSqlField(paramName) + "` BETWEEN " + wrapToStr(vals[0]) + " AND " + wrapToStr(vals[1]);
            }
            return null;
        }
        return "`" + StrUtil.toSqlField(paramName) + "`"+ symbol + wrapToStr(paramVal);
    }

    private static String wrapToStr(Object paramVal){
        if(paramVal instanceof String){
            return "'"+paramVal.toString()+"'";
        }else if(paramVal instanceof Date){
            return "'" + DateUtil.formatDateTime((Date) paramVal) + "'";
        }else{
            return paramVal==null?null:paramVal.toString();
        }
    }

    private static final String BAD_STR = "'and|exec|execute|insert|select|delete|update|count|drop|create|*|%|#|chr|mid|master|truncate|" +
            "char|declare|sitename|net user|xp_cmdshell|;|or|+|like|" +
            "table|from|grant|use|group_concat|column_name|information_schema.columns|table_schema|union|where";
    private static final String[] BAD_STR_LIST = StrUtil.splitToArray(BAD_STR, '|');

    /**
     * 是否存在 SQL注入
     * @param str
     * @return
     */
    public static boolean isIncludeSqlInject(String str) {
        str = str.toLowerCase();//统一转为小写
        for (int i = 0; i < BAD_STR_LIST.length; i++) {
            if (str.indexOf(BAD_STR_LIST[i]) >= 0) {
                LogDeBugUtil.debug(str + " 存在 SQL注入关键字 " + BAD_STR_LIST[i]);
                return true;
            }
        }
        return false;
    }

}
